﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BanHangCshap.Class;
namespace BanHangCshap.Class
{
    class CongNoTheoKi:Database
    {
        #region ***** Fields & Properties *****
        private int _thang;
        public int thang
        {
            get
            {
                return _thang;
            }
            set
            {
                _thang = value;
            }
        }
        private int _nam;
        public int nam
        {
            get
            {
                return _nam;
            }
            set
            {
                _nam = value;
            }
        }
        private string _makhncc;
        public string makhncc
        {
            get
            {
                return _makhncc;
            }
            set
            {
                _makhncc = value;
            }
        }
        private double _nodauki;
        public double nodauki
        {
            get
            {
                return _nodauki;
            }
            set
            {
                _nodauki = value;
            }
        }
        private double _notrongki;
        public double notrongki
        {
            get
            {
                return _notrongki;
            }
            set
            {
                _notrongki = value;
            }
        }
        private double _tratrongki;
        public double tratrongki
        {
            get
            {
                return _tratrongki;
            }
            set
            {
                _tratrongki = value;
            }
        }
        private double _nocuoiki;
        public double nocuoiki
        {
            get
            {
                return _nocuoiki;
            }
            set
            {
                _nocuoiki = value;
            }
        }
        #endregion

        #region ***** Init Methods *****
        public CongNoTheoKi()
        {
        }
        public CongNoTheoKi(int thang)
        {
            this.thang = thang;
        }
        public DataTable layCongNoTheoKi(int thang, int nam)
        {
            string sql = "select " + thang + " as thang," + nam + " as nam,a.makhncc,nodaukikhncc, isnull(tiennotrongki,0) as notrongki, isnull(tientratrongki,0) as tratrongki,(nodaukikhncc + isnull(tiennotrongki,0) - isnull(tientratrongki,0)) as nocuoiki ";
            sql += "from KhachHangNCC a ";
            sql += "left join ";
            sql += "(select makhncc,sum(tongtienso) as tiennotrongki  from PhieuNhapXuat where  month(ngaynhapxuat)= " + thang + " and  ";
            sql += "year(ngaynhapxuat) = " + nam + " group by makhncc) notrongki ";
            sql += "on a.makhncc = notrongki.makhncc left join ";
            sql += "(select makhncc,sum(sotienso) as tientratrongki  from PhieuThuChi where  month(ngaythuchi)= " + thang + " and  ";
            sql += "year(ngaythuchi) = " + nam + " group by makhncc) tratrongki ";
            sql += "on a.makhncc = tratrongki.makhncc ";
            return getDataTable(sql);
        }
        public Boolean capNhatCongNoTheoKi()
        {
            string sql = "insert into CongNoTheoKi values (" + thang + "," + nam + ",'" + makhncc + "'," + nodauki + ", " + notrongki + "," + tratrongki + "," + nocuoiki + ")";
            return executeQueryTrans(sql);
        }
        public DataTable layCongNoChiTietKhachHangNCC(int makhncc)
        {
            string sql = "";
            return getDataTable(sql);
        }
       
        //public Boolean themCongNo(int thang, int nam)
        //{
        //    string sql = "select " + thang + " as thang," + nam + " as nam,a.makhncc,nodaukikhncc, isnull(tiennotrongki,0) as notrongki, isnull(tientratrongki,0) as tratrongki,(nodaukikhncc + isnull(tiennotrongki,0) - isnull(tientratrongki,0)) as nocuoiki ";
        //    sql += "from KhachHangNCC a ";
        //    sql += "left join ";
        //    sql += "(select makhncc,sum(tongtienso) as tiennotrongki  from PhieuNhapXuat where  month(ngaynhapxuat)= " + thang + " and  ";
        //    sql += "year(ngaynhapxuat) = " + nam + " group by makhncc) notrongki ";
        //    sql += "on a.makhncc = notrongki.makhncc left join ";
        //    sql += "(select makhncc,sum(sotienso) as tientratrongki  from PhieuThuChi where  month(ngaythuchi)= " + thang + " and  ";
        //    sql += "year(ngaythuchi) = " + nam + " group by makhncc) tratrongki ";
        //    sql += "on a.makhncc = tratrongki.makhncc ";
        //    DataTable dt = getDataTable(sql);
        //    Boolean ok = true;
        //    //DatabaseStatic.sqltrans = DatabaseStatic.conn.BeginTransaction();
        //    KhachHangNCC clskhncc = new KhachHangNCC();
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        thang = int.Parse(dr["thang"].ToString());
        //        nam = int.Parse(dr["nam"].ToString());
        //        makhncc = int.Parse(dr["makhncc"].ToString());
        //        nodauki = double.Parse(dr["nodaukikhncc"].ToString());
        //        notrongki = double.Parse(dr["notrongki"].ToString());
        //        tratrongki = double.Parse(dr["tratrongki"].ToString());
        //        nocuoiki = double.Parse(dr["nocuoiki"].ToString());

        //        // luu Cong no khach hang/ncc
        //        string sqlcongno = "insert into CongNo values (" + thang + "," + nam + "," + makhncc + "," + nodauki + ", " + notrongki + "," + tratrongki + "," + nocuoiki + ")";
        //        if (executeQueryTrans(sqlcongno) == true)
        //        {
        //            //Cap nhat cong no dau khi khach hang/ncc
        //            if (clskhncc.capNhatCongNoDauKi(makhncc, nocuoiki) == false)
        //            {
        //                ok = false;
        //                break;
        //            }
                    
        //        }
        //        else
        //        {
        //            ok = false;
        //            break;
        //        }
        //        //
        //    }
        //    return ok;

        //}
        //public CongNo(int thang, int nam, int makh, decimal nodauki, decimal notrongki, decimal nocuoiki)
        //{
        //    this.thang = thang;
        //    this.nam = nam;
        //    this.makh = makh;
        //    this.nodauki = nodauki;
        //    this.notrongki = notrongki;
        //    this.nocuoiki = nocuoiki;
        //}
        #endregion

    }
}
